const XLSX = require('xlsx');
const fs = require('fs').promises;
const path = require('path');
const { query: dbQuery } = require('../config/database');
const { logger } = require('../utils/logger');
const MenuItem = require('../models/MenuItem');
const MenuCategory = require('../models/MenuCategory');

/**
 * 菜单导出服务类
 * 处理菜单数据导出和Excel模板生成
 */
class MenuExportService {
  constructor() {
    this.templateColumns = [
      { key: 'ID', name: 'ID', example: '1', note: '数字|可选|用于更新现有菜品' },
      { key: 'Category', name: 'Category', example: 'Main Course', note: '文本|必填|≤200字符|菜品分类' },
      { key: 'Name_English', name: 'Name_English', example: 'Grilled Chicken Rice', note: '文本|必填|≤200字符|英文菜品名' },
      { key: '菜品名称（中文）', name: '菜品名称（中文）', example: '烤鸡饭', note: '文本|可选|≤200字符|中文菜品名' },
      { key: 'Item Description（中文）', name: 'Item Description（中文）', example: '香嫩烤鸡配白米饭', note: '文本|可选|≤5000字符|菜品描述' },
      { key: 'Main Ingredients（中文）', name: 'Main Ingredients（中文）', example: '鸡肉,大米,洋葱', note: '文本|可选|≤1000字符|主要食材' },
      { key: 'Price', name: 'Price', example: '12.80', note: '数字|可选|0-9999.99|菜品价格' },
      { key: 'Quantity Selection', name: 'Quantity Selection', example: '6 pieces / 10 pieces', note: '文本|可选|数量规格选项' },
      { key: 'Meat Selection 1', name: 'Meat Selection 1', example: 'Chicken', note: '文本|可选|肉类选择1' },
      { key: 'Meat Selection 2', name: 'Meat Selection 2', example: 'Beef', note: '文本|可选|肉类选择2' },
      { key: 'Meat Selection 3', name: 'Meat Selection 3', example: 'Beef', note: '文本|可选|肉类选择3' },
      { key: 'Available', name: 'Available', example: 'true', note: '布尔|可选|true/false|是否可用' },
      { key: 'Recommended', name: 'Recommended', example: 'false', note: '布尔|可选|true/false|是否推荐' },
      { key: 'Spicy', name: 'Spicy', example: 'false', note: '布尔|可选|true/false|是否辣味' },
      { key: 'Vegetarian', name: 'Vegetarian', example: 'false', note: '布尔|可选|true/false|是否素食' },
      { key: 'Preparation Time', name: 'Preparation Time', example: '15', note: '数字|可选|制作时间(分钟)' },
      { key: 'Calories', name: 'Calories', example: '450', note: '数字|可选|卡路里' },
      { key: 'Allergens', name: 'Allergens', example: 'Gluten,Dairy', note: '文本|可选|过敏原信息' },
      { key: 'Image URL', name: 'Image URL', example: 'https://example.com/image.jpg', note: '文本|仅导出|图片链接' }
    ];
  }

  /**
   * 生成Excel模板
   */
  async generateTemplate() {
    try {
      logger.info('开始生成菜单导入模板');
      
      // 创建工作簿
      const workbook = XLSX.utils.book_new();
      
      // 准备模板数据
      const templateData = [];
      
      // 标题行
      const headers = this.templateColumns.map(col => col.name);
      templateData.push(headers);
      
      // 示例和注释行
      const examples = this.templateColumns.map(col => col.example);
      const notes = this.templateColumns.map(col => col.note);
      templateData.push(examples);
      templateData.push(notes);
      
      // 添加几行空数据供填写
      for (let i = 0; i < 5; i++) {
        const emptyRow = new Array(this.templateColumns.length).fill('');
        templateData.push(emptyRow);
      }
      
      // 创建工作表
      const worksheet = XLSX.utils.aoa_to_sheet(templateData);
      
      // 设置列宽
      const colWidths = this.templateColumns.map(col => {
        const maxLength = Math.max(
          col.name.length,
          col.example.length,
          col.note.length
        );
        return { wch: Math.min(Math.max(maxLength + 2, 15), 50) };
      });
      worksheet['!cols'] = colWidths;
      
      // 设置行高
      worksheet['!rows'] = [
        { hpt: 20 }, // 标题行
        { hpt: 20 }, // 示例行
        { hpt: 30 }  // 注释行
      ];
      
      // 添加工作表到工作簿
      XLSX.utils.book_append_sheet(workbook, worksheet, 'Menu Template');
      
      // 添加元数据工作表
      const metadataSheet = this.createMetadataSheet();
      XLSX.utils.book_append_sheet(workbook, metadataSheet, 'Metadata');
      
      // 生成Excel缓冲区
      const buffer = XLSX.write(workbook, { 
        type: 'buffer', 
        bookType: 'xlsx',
        compression: true
      });
      
      logger.info('菜单导入模板生成完成');
      
      return {
        buffer,
        filename: `menu_template_${new Date().toISOString().split('T')[0]}.xlsx`,
        contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      };
    } catch (error) {
      logger.error('生成模板失败:', error);
      throw error;
    }
  }

  /**
   * 创建元数据工作表
   */
  createMetadataSheet() {
    const metadata = [
      ['Template Information', ''],
      ['Template Version', '1.0.0'],
      ['Generated At', new Date().toISOString()],
      ['System', 'CMB Menu Management'],
      ['Timezone', 'Asia/Singapore'],
      ['Encoding', 'UTF-8'],
      ['', ''],
      ['Field Descriptions', ''],
      ['Field Name', 'Description'],
      ...this.templateColumns.map(col => [col.name, col.note])
    ];
    
    const worksheet = XLSX.utils.aoa_to_sheet(metadata);
    
    // 设置列宽
    worksheet['!cols'] = [
      { wch: 25 },
      { wch: 60 }
    ];
    
    return worksheet;
  }

  /**
   * 导出菜单数据
   */
  async exportMenuData(filters = {}) {
    try {
      logger.info('开始导出菜单数据', filters);
      
      // 构建查询条件
      const whereConditions = ['mi.deleted_at IS NULL'];
      const queryParams = [];
      
      // 分类过滤
      if (filters.category_id) {
        whereConditions.push('mi.category_id = ?');
        queryParams.push(filters.category_id);
      }
      
      if (filters.category_name) {
        whereConditions.push('(mc.name LIKE ? OR mc.name_en LIKE ?)');
        queryParams.push(`%${filters.category_name}%`, `%${filters.category_name}%`);
      }
      
      // 时间过滤
      if (filters.created_after) {
        whereConditions.push('mi.created_at >= ?');
        queryParams.push(filters.created_after);
      }
      
      if (filters.created_before) {
        whereConditions.push('mi.created_at <= ?');
        queryParams.push(filters.created_before);
      }
      
      // 可用性过滤
      if (filters.is_available !== undefined) {
        whereConditions.push('mi.is_available = ?');
        queryParams.push(filters.is_available);
      }
      
      // 查询菜单数据
      const menuQuery = `
        SELECT 
          mi.id,
          mc.name as category_name,
          mc.name_en as category_name_en,
          mi.name,
          mi.name_en,
          mi.description,
          mi.main_ingredients,
          mi.price,
          mi.quantity_selection,
          mi.is_available,
          mi.is_recommended,
          mi.is_spicy,
          mi.is_vegetarian,
          mi.preparation_time,
          mi.calories,
          mi.allergens,
          mi.image_url,
          mi.created_at,
          mi.updated_at
        FROM menu_items mi
        LEFT JOIN menu_categories mc ON mi.category_id = mc.id
        WHERE ${whereConditions.join(' AND ')}
        ORDER BY mc.sort_order ASC, mi.sort_order ASC, mi.name_en ASC
      `;
      
      const menuItems = await dbQuery(menuQuery, queryParams);
      
      // 查询肉类选择
      const meatOptionsQuery = `
        SELECT 
          mo.menu_item_id,
          mo.option_name,
          mo.sort_order
        FROM menu_options mo
        WHERE mo.option_group = 'Meat' 
          AND mo.menu_item_id IN (${menuItems.map(() => '?').join(',')})
          AND mo.deleted_at IS NULL
        ORDER BY mo.menu_item_id, mo.sort_order
      `;
      
      const meatOptions = menuItems.length > 0 
        ? await dbQuery(meatOptionsQuery, menuItems.map(item => item.id))
        : [];
      
      // 组织肉类选择数据
      const meatOptionsMap = new Map();
      meatOptions.forEach(option => {
        if (!meatOptionsMap.has(option.menu_item_id)) {
          meatOptionsMap.set(option.menu_item_id, []);
        }
        meatOptionsMap.get(option.menu_item_id).push(option.option_name);
      });
      
      // 转换为导出格式
      const exportData = menuItems.map(item => {
        const meatSelections = meatOptionsMap.get(item.id) || [];
        
        return {
          'ID': item.id,
          'Category': item.category_name || item.category_name_en,
          'Name_English': item.name_en,
          '菜品名称（中文）': item.name,
          'Item Description（中文）': item.description,
          'Main Ingredients（中文）': item.main_ingredients,
          'Price': item.price,
          'Quantity Selection': item.quantity_selection,
          'Meat Selection 1': meatSelections[0] || '',
          'Meat Selection 2': meatSelections[1] || '',
          'Meat Selection 3': meatSelections[2] || '',
          'Available': item.is_available ? 'true' : 'false',
          'Recommended': item.is_recommended ? 'true' : 'false',
          'Spicy': item.is_spicy ? 'true' : 'false',
          'Vegetarian': item.is_vegetarian ? 'true' : 'false',
          'Preparation Time': item.preparation_time,
          'Calories': item.calories,
          'Allergens': item.allergens,
          'Image URL': item.image_url
        };
      });
      
      // 创建Excel文件
      const workbook = XLSX.utils.book_new();
      
      // 创建数据工作表
      const worksheet = XLSX.utils.json_to_sheet(exportData, {
        header: this.templateColumns.map(col => col.name)
      });
      
      // 设置列宽
      const colWidths = this.templateColumns.map(col => {
        const maxLength = Math.max(
          col.name.length,
          ...exportData.map(row => String(row[col.name] || '').length)
        );
        return { wch: Math.min(Math.max(maxLength + 2, 15), 50) };
      });
      worksheet['!cols'] = colWidths;
      
      // 添加工作表
      XLSX.utils.book_append_sheet(workbook, worksheet, 'Menu Data');
      
      // 添加统计信息工作表
      const statsSheet = this.createStatsSheet(menuItems, filters);
      XLSX.utils.book_append_sheet(workbook, statsSheet, 'Export Info');
      
      // 生成Excel缓冲区
      const buffer = XLSX.write(workbook, { 
        type: 'buffer', 
        bookType: 'xlsx',
        compression: true
      });
      
      logger.info(`菜单数据导出完成，共${exportData.length}条记录`);
      
      return {
        buffer,
        filename: `menu_export_${new Date().toISOString().split('T')[0]}.xlsx`,
        contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        recordCount: exportData.length
      };
    } catch (error) {
      logger.error('导出菜单数据失败:', error);
      throw error;
    }
  }

  /**
   * 创建统计信息工作表
   */
  createStatsSheet(menuItems, filters) {
    const stats = [
      ['Export Information', ''],
      ['Export Date', new Date().toISOString()],
      ['Total Records', menuItems.length],
      ['Timezone', 'Asia/Singapore'],
      ['', ''],
      ['Applied Filters', ''],
      ['Category ID', filters.category_id || 'All'],
      ['Category Name', filters.category_name || 'All'],
      ['Created After', filters.created_after || 'No limit'],
      ['Created Before', filters.created_before || 'No limit'],
      ['Available Only', filters.is_available !== undefined ? (filters.is_available ? 'Yes' : 'No') : 'All'],
      ['', ''],
      ['Statistics', ''],
      ['Available Items', menuItems.filter(item => item.is_available).length],
      ['Recommended Items', menuItems.filter(item => item.is_recommended).length],
      ['Spicy Items', menuItems.filter(item => item.is_spicy).length],
      ['Vegetarian Items', menuItems.filter(item => item.is_vegetarian).length],
      ['Items with Images', menuItems.filter(item => item.image_url).length]
    ];
    
    const worksheet = XLSX.utils.aoa_to_sheet(stats);
    
    // 设置列宽
    worksheet['!cols'] = [
      { wch: 20 },
      { wch: 30 }
    ];
    
    return worksheet;
  }

  /**
   * 获取可用分类列表
   */
  async getAvailableCategories() {
    try {
      const categories = await MenuCategory.findAll({ is_active: true });
      return categories.map(cat => ({
        id: cat.id,
        name: cat.name,
        name_en: cat.name_en
      }));
    } catch (error) {
      logger.error('获取分类列表失败:', error);
      throw error;
    }
  }

  /**
   * 验证导出参数
   */
  validateExportParams(params) {
    const errors = [];
    
    // 验证日期格式
    if (params.created_after && !this.isValidDate(params.created_after)) {
      errors.push('created_after 日期格式无效');
    }
    
    if (params.created_before && !this.isValidDate(params.created_before)) {
      errors.push('created_before 日期格式无效');
    }
    
    // 验证日期范围
    if (params.created_after && params.created_before) {
      const after = new Date(params.created_after);
      const before = new Date(params.created_before);
      if (after > before) {
        errors.push('created_after 不能晚于 created_before');
      }
    }
    
    // 验证分类ID
    if (params.category_id && (!Number.isInteger(Number(params.category_id)) || Number(params.category_id) <= 0)) {
      errors.push('category_id 必须是正整数');
    }
    
    return errors;
  }

  /**
   * 验证日期格式
   */
  isValidDate(dateString) {
    const date = new Date(dateString);
    return date instanceof Date && !isNaN(date.getTime());
  }

  /**
   * 生成导出统计报告
   */
  async generateExportStats(startDate, endDate) {
    try {
      const query = `
        SELECT 
          DATE(created_at) as export_date,
          COUNT(*) as export_count,
          SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as successful_exports
        FROM audit_logs 
        WHERE action = 'menu_export'
          AND created_at BETWEEN ? AND ?
        GROUP BY DATE(created_at)
        ORDER BY export_date DESC
      `;
      
      const stats = await dbQuery(query, [startDate, endDate]);
      
      return {
        period: { start: startDate, end: endDate },
        daily_stats: stats,
        total_exports: stats.reduce((sum, day) => sum + day.export_count, 0),
        successful_exports: stats.reduce((sum, day) => sum + day.successful_exports, 0)
      };
    } catch (error) {
      logger.error('生成导出统计失败:', error);
      throw error;
    }
  }

  async exportMenuV2(filters = {}) {
    try {
      logger.info('开始导出V2菜单数据', filters);

      // 1) 查询菜品（使用现有 menu_items 与 menu_categories）
      const whereConditions = ['mi.deleted_at IS NULL'];
      const params = [];
      if (filters.category_id) { whereConditions.push('mi.category_id = ?'); params.push(filters.category_id); }
      if (filters.category_name) { whereConditions.push('(mc.name LIKE ? OR mc.name_en LIKE ?)'); params.push(`%${filters.category_name}%`, `%${filters.category_name}%`); }
      if (filters.created_after) { whereConditions.push('mi.created_at >= ?'); params.push(filters.created_after); }
      if (filters.created_before) { whereConditions.push('mi.created_at <= ?'); params.push(filters.created_before); }
      if (filters.is_available !== undefined) { whereConditions.push('mi.is_available = ?'); params.push(filters.is_available); }

      const itemsSql = `
        SELECT 
          mi.id,
          mi.name,
          mi.name_en,
          mi.description,
          mi.description_en,
          mi.main_ingredients,
          mi.normalized_name,
          mi.is_available,
          mi.tags,
          mi.created_at,
          mi.updated_at,
          mc.id AS category_id,
          mc.name AS category_name,
          mc.name_en AS category_name_en
        FROM menu_items mi
        LEFT JOIN menu_categories mc ON mc.id = mi.category_id
        WHERE ${whereConditions.join(' AND ')}
        ORDER BY mc.sort_order ASC, mi.sort_order ASC, mi.name_en ASC
      `;

      const dishes = await dbQuery(itemsSql, params);
      const dishIds = dishes.map(d => d.id);

      // 2) 查询 variants / options（保持现有字段，新增字段导出为空占位）
      const variants = dishIds.length ? await dbQuery(
        `SELECT id, menu_item_id, variant_name, variant_name_en, price, sort_order, deleted_at
         FROM menu_variants
         WHERE deleted_at IS NULL AND menu_item_id IN (${dishIds.map(() => '?').join(',')})
         ORDER BY menu_item_id, sort_order, id`,
        dishIds
      ) : [];

      const options = dishIds.length ? await dbQuery(
        `SELECT id, menu_item_id, option_group, option_name, option_name_en, price_adjustment, sort_order, deleted_at
         FROM menu_options
         WHERE deleted_at IS NULL AND menu_item_id IN (${dishIds.map(() => '?').join(',')})
         ORDER BY menu_item_id, option_group, sort_order, id`,
        dishIds
      ) : [];

      // 3) 严格按新三表结构组织
      const { SCHEMA, slugify } = require('../constants/importV2Schema');
      const toSku = (mi) => mi.normalized_name && mi.normalized_name.trim() ? mi.normalized_name : `SKU-${mi.id}`;

      // Dishes
      const dishesSheet = [];
      const dishHeaders = SCHEMA.Dishes;
      dishesSheet.push(dishHeaders);

      for (const mi of dishes) {
        // 解析 tags（JSON -> 逗号分隔字符串）
        let tagsStr = '';
        try {
          if (mi.tags) {
            const t = typeof mi.tags === 'string' ? JSON.parse(mi.tags) : mi.tags;
            if (Array.isArray(t)) tagsStr = t.map(s => String(s).trim()).filter(Boolean).join(',');
          }
        } catch (_) { /* ignore parse error */ }

        const rowObj = {
          dish_sku: toSku(mi),
          dish_slug: slugify(mi.name_en || mi.name || ''),
          name_zh: mi.name || '',
          name_en: mi.name_en || '',
          category: mi.category_name || mi.category_name_en || '',
          base_status: mi.is_available ? 'active' : 'inactive',
          description_zh: mi.description || '',
          description_en: mi.description_en || '',
          ingredients_zh: mi.main_ingredients || '',
          ingredients_en: '',
          tags: tagsStr,
          popularity: ''
        };
        dishesSheet.push(dishHeaders.map(h => rowObj[h] ?? ''));
      }

      // Variants
      const variantsSheet = [];
      const variantHeaders = SCHEMA.Variants;
      variantsSheet.push(variantHeaders);

      const miSkuMap = new Map(dishes.map(mi => [mi.id, toSku(mi)]));
      for (const v of variants) {
        const code = (v.variant_name_en || v.variant_name || '')
          .toString().trim()
          .replace(/\s+/g, '_')
          .replace(/[^A-Za-z0-9_\-]/g, '')
          .toUpperCase();
        const rowObj = {
          dish_sku: miSkuMap.get(v.menu_item_id) || '',
          name_zh: v.variant_name || '',
          variant_code: code || '',
          variant_name: v.variant_name_en || v.variant_name || '',
          price: v.price != null ? String(v.price) : '',
          compare_at_price: '',
          is_default: '',
          stock: ''
        };
        variantsSheet.push(variantHeaders.map(h => rowObj[h] ?? ''));
      }

      // Options
      const optionsSheet = [];
      const optionHeaders = SCHEMA.Options;
      optionsSheet.push(optionHeaders);

      for (const op of options) {
        const groupCode = (op.option_group || '')
          .toString().trim()
          .replace(/\s+/g, '_')
          .replace(/[^A-Za-z0-9_\-]/g, '')
          .toUpperCase();
        const valueCode = (op.option_name_en || op.option_name || '')
          .toString().trim()
          .replace(/\s+/g, '_')
          .replace(/[^A-Za-z0-9_\-]/g, '')
          .toUpperCase();
        const rowObj = {
          dish_sku: miSkuMap.get(op.menu_item_id) || '',
          name_zh: '',
          group_code: groupCode || '',
          group_name: op.option_group || '',
          required: '',
          value_code: valueCode || '',
          value_name: op.option_name || op.option_name_en || '',
          price_delta: op.price_adjustment != null ? String(op.price_adjustment) : ''
        };
        optionsSheet.push(optionHeaders.map(h => rowObj[h] ?? ''));
      }

      // 4) 写入Excel（三个 Sheet，严格命名）
      const workbook = XLSX.utils.book_new();
      const dishesWs = XLSX.utils.aoa_to_sheet(dishesSheet);
      const variantsWs = XLSX.utils.aoa_to_sheet(variantsSheet);
      const optionsWs = XLSX.utils.aoa_to_sheet(optionsSheet);
      XLSX.utils.book_append_sheet(workbook, dishesWs, 'Dishes');
      XLSX.utils.book_append_sheet(workbook, variantsWs, 'Variants');
      XLSX.utils.book_append_sheet(workbook, optionsWs, 'Options');

      const outDir = path.join(process.cwd(), 'uploads', 'exports');
      await fs.mkdir(outDir, { recursive: true });
      const filePath = path.join(outDir, `menu_export_v2_${Date.now()}.xlsx`);
      XLSX.writeFile(workbook, filePath);

      logger.info('V2菜单数据导出完成', { filePath });
      return filePath;
    } catch (error) {
      logger.error('导出V2菜单数据失败:', error);
      throw error;
    }
  }
}

module.exports = MenuExportService;